require('dotenv').config()
const express = require('express')
const mysql = require('mysql2/promise')
const bodyParser = require('body-parser')
const cors = require('cors')

const app = express()
app.use(cors())
app.use(bodyParser.json())

// Create database connection pool
const db = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT,
})

// Get all todos
app.get('/todos', async (req, res) => {
  try {
    const [results] = await db.query('SELECT * FROM todos ORDER BY sortNum ASC')
    res.json(results)
  } catch (err) {
    res.status(500).json({ error: err })
  }
})

// Add a new todo
app.post('/todos', async (req, res) => {
  const { text, sortNum } = req.body
  try {
    const [results] = await db.query('INSERT INTO todos (text, sortNum) VALUES (?, ?)', [
      text,
      sortNum,
    ])
    res.json({ id: results.insertId, text, sortNum })
  } catch (err) {
    res.status(500).json({ error: err })
  }
})

// Delete a todo
app.delete('/todos/:id', async (req, res) => {
  try {
    await db.query('DELETE FROM todos WHERE id = ?', [req.params.id])
    res.json({ message: '删除成功' })
  } catch (err) {
    res.status(500).json({ error: err })
  }
})

// Update a todo's sort number
app.put('/todos/:id', async (req, res) => {
  const { sortNum } = req.body
  const id = req.params.id
  try {
    await db.query('UPDATE todos SET sortNum = ? WHERE id = ?', [sortNum, id])
    res.json({ message: 'Sort number updated', id, sortNum })
  } catch (err) {
    res.status(500).json({ error: err })
  }
})

// Get status config
app.get('/statusconfig', async (req, res) => {
  try {
    const [result] = await db.query('SELECT * FROM statusconfig')
    res.json(result)
  } catch (err) {
    res.status(500).json({ error: 'Failed to fetch status config', details: err })
  }
})

// Get tasks
app.get('/tasks', async (req, res) => {
  try {
    const [rows] = await db.query('SELECT * FROM tasks')
    res.json(rows)
  } catch (err) {
    res.status(500).send('获取任务列表失败')
  }
})

async function fetchCategoryTree() {
  const [rows] = await db.query('SELECT * FROM categories')
  const data = rows.map((row) => ({
    id: row.id,
    name: row.name,
    parent_id: row.parent_id,
    description: row.description,
  }))

  // Build tree structure
  const buildTree = (items, parentId = null) =>
    items
      .filter((item) => item.parent_id === parentId)
      .map((item) => ({
        ...item,
        children: buildTree(items, item.id),
      }))

  return buildTree(data)
}

app.get('/categories', async (req, res) => {
  try {
    const categoryTree = await fetchCategoryTree()
    res.json(categoryTree)
  } catch (err) {
    console.error(err)
    res.status(500).send('Error fetching categories')
  }
})

// Start the server
app.listen(3000, () => {
  console.log('Server is running on http://localhost:3000')
})
